import db = require('../../../../platform/server/data');
import platform = require('../../../../platform/common/baseCommon');
import appServer = require('../../../../platform/server/appServer');

let service = new db.dataService();

service.connectionInfo = new db.dataConnectionInfo();

service.connectionInfo.dbConnection = {
    type: "mysql",
    host: "192.168.5.73",
    //host: 'localhost',
    port: 3306,
    database: "world",
    username: "root",
    password: "root",
    autoSchemaSync: false
};
service.commandList = [];

/**
 * 命令:城市人口查询
 */
let cmd_city_population = new db.dataCommandInfo();
cmd_city_population.id = "cmd_city_population";
cmd_city_population.name = "城市人口";
cmd_city_population.sql = `SELECT t.*,c.name as 'countryname' FROM world.city t,world.country c where t.CountryCode = c.code 
and {0} order by t.district`;
/**
 * 数据过滤器
 */
let datafilter = new db.baseDataFilter()
    .and(new db.sqlFilter("name", "c.name=?", "国家"),
    new db.sqlFilter("district", "t.district=?", "省份"));
cmd_city_population.dataFilter = [];
cmd_city_population.dataFilter.push(datafilter);

/** 国家省份信息 */
let cmd_city_district = new db.dataCommandInfo();
cmd_city_district.id = "cmd_city_district";
cmd_city_district.name = "国家省份";
cmd_city_district.sql = `SELECT t.code,t.name,t.continent,c.district,t.governmentform 
    FROM world.country t,world.city c
    where t.code = c.countrycode and {0}
    group by c.district
    order by t.code`;
let datafilter2 = new db.baseDataFilter()
    .and(new db.sqlFilter("name", "t.name=?", "国家"),
    new db.sqlFilter("district", "c.district=?", "省份"));
cmd_city_district.dataFilter = [];
cmd_city_district.dataFilter.push(datafilter2);
/** 纳税人信息 */
let cmd_dj_nsrxx = new db.dataCommandInfo();
cmd_dj_nsrxx.id = "cmd_dj_nsrxx";
cmd_dj_nsrxx.name = "纳税人信息";
cmd_dj_nsrxx.sql = `SELECT * FROM example.dj_nsrxx`;



/** 国家省份信息 */
let cmd_country = new db.dataCommandInfo();
cmd_country.id = "cmd_country";
cmd_country.name = "国家省份";
cmd_country.sql = `SELECT * FROM world.country t
    where {0} and t.population>60000000
    order by t.code`;
// cmd_country.sql = `SELECT  cname AS "姓名",
// SUM(IF(cource="语文",score,0)) AS "语文",
// SUM(IF(cource="数学",score,0)) AS "数学",
// SUM(IF(cource="物理",score,0)) AS "物理"
// FROM tb GROUP BY cname`;
let datafilter3 = new db.baseDataFilter()
    .and(new db.sqlFilter("name", "t.name=?", "国家"));
cmd_country.dataFilter = [datafilter3];

service.commandList.push(cmd_city_population, cmd_dj_nsrxx, cmd_city_district, cmd_country);

/**
 * 用户角色信息
 */
let cmd_user_role = new db.dataCommandInfo();
cmd_user_role.id = 'cmd_user_role';
cmd_user_role.name = '用户角色表';
cmd_user_role.sql = `SELECT UR.USER_ID,U.NAME USER_NAME,U.PASSWORD,UR.ROLE_ID,R.NAME ROLE_NAME FROM WEBPT.PT_USER U 
join webpt.pt_userrole UR ON U.ID=UR.USER_ID 
join webpt.pt_role R ON UR.ROLE_ID=R.ID WHERE {0}`;
/** 数据过滤器 */
let filter_user_role = new db.baseDataFilter()
    .and(
    new db.sqlFilter("USER_ID", "UR.USER_ID=?", "用户ID"),
    new db.sqlFilter("ROLE_ID", "UR.ORLE_ID=?", "角色ID")
    );
cmd_user_role.dataFilter = [];
cmd_user_role.dataFilter.push(filter_user_role);

platform.fetchAddonProtoType(service);
export let dataServiceInfo = new appServer.serviceInfo();
dataServiceInfo.serviceName = "example";
dataServiceInfo.serviceObject = service;


